﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DbHelper;//自定义帮助类
using Gx.Model.Entity;//实体
using Gx.Common;//通用类
namespace Gx.Dal.Employee
{
    public partial class G_StockOutBackGoodsInfoDal : SqlServerHelper, IDisposable
    {

        public bool AddG_StockOutBackGoodsInfo(G_StockOutBackGoodsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO G_StockOutBackGoodsInfo");
            strSql.Append("(");
            strSql.Append("StockOutBackId,");
            strSql.Append("GoodsId,");
            strSql.Append("GoodsName,");
            strSql.Append("GoodsNum,");
            strSql.Append("UnitValue");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@StockOutBackId,");
            strSql.Append("@GoodsId,");
            strSql.Append("@GoodsName,");
            strSql.Append("@GoodsNum,");
            strSql.Append("@UnitValue");
            strSql.Append(")");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockOutBackId", SqlDbType.Int, 4) { Value = model.StockOutBackId });
            pList.Add(new SqlParameter("@GoodsId", SqlDbType.Int, 4) { Value = model.GoodsId });
            pList.Add(new SqlParameter("@GoodsName", SqlDbType.NVarChar, 50) { Value = model.GoodsName });
            pList.Add(new SqlParameter("@GoodsNum", SqlDbType.Decimal, 9) { Value = model.GoodsNum });
            pList.Add(new SqlParameter("@UnitValue", SqlDbType.Int, 4) { Value = model.UnitValue });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public bool DelG_StockOutBackGoodsInfo(int StockOutBackGoodsId)
        {
            string strSql = "DELETE FROM G_StockOutBackGoodsInfo WHERE StockOutBackGoodsId=@StockOutBackGoodsId";
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockOutBackGoodsId", SqlDbType.Int, 4) { Value = StockOutBackGoodsId });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }


        public bool EditG_StockOutBackGoodsInfo(G_StockOutBackGoodsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE G_StockOutBackGoodsInfo SET ");
            strSql.Append(" StockOutBackId=@StockOutBackId,");
            strSql.Append(" GoodsId=@GoodsId,");
            strSql.Append(" GoodsName=@GoodsName,");
            strSql.Append(" GoodsNum=@GoodsNum,");
            strSql.Append(" UnitValue=@UnitValue");
            strSql.Append(" WHERE StockOutBackGoodsId=@StockOutBackGoodsId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockOutBackGoodsId", SqlDbType.Int, 4) { Value = model.StockOutBackGoodsId });
            pList.Add(new SqlParameter("@StockOutBackId", SqlDbType.Int, 4) { Value = model.StockOutBackId });
            pList.Add(new SqlParameter("@GoodsId", SqlDbType.Int, 4) { Value = model.GoodsId });
            pList.Add(new SqlParameter("@GoodsName", SqlDbType.NVarChar, 50) { Value = model.GoodsName });
            pList.Add(new SqlParameter("@GoodsNum", SqlDbType.Decimal, 9) { Value = model.GoodsNum });
            pList.Add(new SqlParameter("@UnitValue", SqlDbType.Int, 4) { Value = model.UnitValue });
            return ExecuteNonQuery(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()) > 0;
        }

        public List<G_StockOutBackGoodsInfo> GetG_StockOutBackGoodsInfoList(string strWhere)
        {
            List<G_StockOutBackGoodsInfo> list = new List<G_StockOutBackGoodsInfo>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append(" StockOutBackGoodsId");
            strSql.Append(",StockOutBackId");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",GoodsNum");
            strSql.Append(",UnitValue");
            strSql.AppendFormat(" FROM G_StockOutBackGoodsInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StockOutBackGoodsId = r.GetOrdinal("StockOutBackGoodsId");
                int StockOutBackId = r.GetOrdinal("StockOutBackId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int GoodsNum = r.GetOrdinal("GoodsNum");
                int UnitValue = r.GetOrdinal("UnitValue");
                G_StockOutBackGoodsInfo model = null;
                while (r.Read())
                {
                    model = new G_StockOutBackGoodsInfo();
                    model.StockOutBackGoodsId = r.GetInt32(StockOutBackGoodsId);
                    model.StockOutBackId = r.GetInt32(StockOutBackId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.GoodsNum = r.GetDecimal(GoodsNum);
                    model.UnitValue = r.GetInt32(UnitValue);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }

        public List<G_StockOutBackGoodsInfo> GetG_StockOutBackGoodsInfoList(string strWhere, int pageIndex, int pageSize, out int totalCount)
        {
            List<G_StockOutBackGoodsInfo> list = new List<G_StockOutBackGoodsInfo>();
            totalCount = GetG_StockOutBackGoodsInfoCount(strWhere);
            if (totalCount == 0)
            {
                return list;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (");
            strSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY StockOutBackGoodsId DESC) AS ORDERBYID");
            strSql.Append(",StockOutBackGoodsId");
            strSql.Append(",StockOutBackId");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",GoodsNum");
            strSql.Append(",UnitValue");
            strSql.AppendFormat(" FROM G_StockOutBackGoodsInfo WITH(NOLOCK) WHERE 1=1 {0}) AS T WHERE T.ORDERBYID  BETWEEN {1} AND {2}", strWhere, pageIndex, pageSize);
            using (SqlDataReader r = ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString()))
            {
                int StockOutBackGoodsId = r.GetOrdinal("StockOutBackGoodsId");
                int StockOutBackId = r.GetOrdinal("StockOutBackId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int GoodsNum = r.GetOrdinal("GoodsNum");
                int UnitValue = r.GetOrdinal("UnitValue");
                G_StockOutBackGoodsInfo model = null;
                while (r.Read())
                {
                    model = new G_StockOutBackGoodsInfo();
                    model.StockOutBackGoodsId = r.GetInt32(StockOutBackGoodsId);
                    model.StockOutBackId = r.GetInt32(StockOutBackId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.GoodsNum = r.GetDecimal(GoodsNum);
                    model.UnitValue = r.GetInt32(UnitValue);
                    list.Add(model);
                }
                r.Close();
            }
            return list;
        }
       
        public int GetG_StockOutBackGoodsInfoCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("SELECT COUNT(*) FROM G_StockOutBackGoodsInfo WITH(NOLOCK) WHERE 1=1 {0}", strWhere);
            return int.Parse(ExecuteScalar(ConfigHelper.ConnectionStrings, strSql.ToString()).ToString());
        }

        public G_StockOutBackGoodsInfo GetG_StockOutBackGoodsInfoModel(int stockOutBackGoodsId)
        {
            G_StockOutBackGoodsInfo model = new G_StockOutBackGoodsInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("StockOutBackGoodsId");
            strSql.Append(",StockOutBackId");
            strSql.Append(",GoodsId");
            strSql.Append(",GoodsName");
            strSql.Append(",GoodsNum");
            strSql.Append(",UnitValue");
            strSql.Append(" FROM G_StockOutBackGoodsInfo WITH(NOLOCK) WHERE StockOutBackGoodsId=@StockOutBackGoodsId");
            List<SqlParameter> pList = new List<SqlParameter>();
            pList.Add(new SqlParameter("@StockOutBackGoodsId", SqlDbType.Int, 4) { Value = stockOutBackGoodsId });
            using (SqlDataReader r = this.ExecuteReader(ConfigHelper.ConnectionStrings, strSql.ToString(), pList.ToArray()))
            {
                int StockOutBackGoodsId = r.GetOrdinal("StockOutBackGoodsId");
                int StockOutBackId = r.GetOrdinal("StockOutBackId");
                int GoodsId = r.GetOrdinal("GoodsId");
                int GoodsName = r.GetOrdinal("GoodsName");
                int GoodsNum = r.GetOrdinal("GoodsNum");
                int UnitValue = r.GetOrdinal("UnitValue");
                if (r.Read())
                {
                    model.StockOutBackGoodsId = r.GetInt32(StockOutBackGoodsId);
                    model.StockOutBackId = r.GetInt32(StockOutBackId);
                    model.GoodsId = r.GetInt32(GoodsId);
                    model.GoodsName = r.GetString(GoodsName);
                    model.GoodsNum = r.GetDecimal(GoodsNum);
                    model.UnitValue = r.GetInt32(UnitValue);
                }
                r.Close();
            }
            return model;
        }
    }
}
